Data Types

您所在的位置：网站首页 › varchar decimal › Data Types

Data Types

2023-11-19 14:04| 来源: 网络整理| 查看: 265

Data Types

Sequelize provides a lot of built-in data types. To access a built-in data type, you must import DataTypes:

// Import the built-in data typesimport { DataTypes } from '@sequelize/core';

Below is a series of support table describing which SQL Type is used for each Sequelize DataType.

info

Most of our DataTypes also accept option bags. Click on one of our DataTypes in the tables below to view their signature.

A ❌ means the dialect does not support that DataType.

StringsSequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiSTRINGVARCHAR(255)VARCHAR(255)VARCHAR(255)NVARCHAR(255)TEXTVARCHAR(255)VARCHAR(255)VARCHAR(255)STRING(100)VARCHAR(100)VARCHAR(100)VARCHAR(100)NVARCHAR(100)TEXTVARCHAR(100)VARCHAR(100)VARCHAR(100)STRING.BINARY❌VARCHAR(255) BINARYVARCHAR(255) BINARY❌TEXT COLLATE BINARYVARCHAR(255) BINARYVARCHAR(255) FOR BIT DATAVARCHAR(255) FOR BIT DATASTRING(100).BINARY❌VARCHAR(100) BINARYVARCHAR(100) BINARY❌TEXT COLLATE BINARYVARCHAR(100) BINARYVARCHAR(100) FOR BIT DATAVARCHAR(100) FOR BIT DATATEXTTEXTTEXTTEXTNVARCHAR(MAX)TEXTTEXTCLOB(2147483647)CLOB(2147483647)TEXT('tiny')TEXTTINYTEXTTINYTEXTNVARCHAR(256)TEXTTEXTVARCHAR(256)VARCHAR(256)TEXT('medium')TEXTMEDIUMTEXTMEDIUMTEXTNVARCHAR(MAX)TEXTTEXTVARCHAR(16777216)VARCHAR(16777216)TEXT('long')TEXTLONGTEXTLONGTEXTNVARCHAR(MAX)TEXTTEXTCLOB(2147483647)CLOB(2147483647)CHARCHAR(255)CHAR(255)CHAR(255)CHAR(255)❌CHAR(255)CHAR(255)CHAR(255)CHAR(100)CHAR(100)CHAR(100)CHAR(100)CHAR(100)❌CHAR(100)CHAR(100)CHAR(100)CHAR.BINARY❌CHAR(255) BINARYCHAR(255) BINARY❌❌CHAR(255) BINARYCHAR(255) FOR BIT DATACHAR(255) FOR BIT DATACHAR(100).BINARY❌CHAR(100) BINARYCHAR(100) BINARY❌❌CHAR(100) BINARYCHAR(255) FOR BIT DATACHAR(255) FOR BIT DATACITEXTCITEXT❌❌❌TEXT COLLATE NOCASE❌❌❌TSVECTORTSVECTOR❌❌❌❌❌❌❌BooleanSequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiBOOLEANBOOLEANTINYINT(1)TINYINT(1)BITTINYINT(1)BOOLEANBOOLEANSMALLINTIntegersSequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiTINYINTSMALLINTints-1TINYINTTINYINTSMALLINTmssql-1INTEGERINTEGERSMALLINTints-1SMALLINTints-1TINYINT(1)❌TINYINT(1)TINYINT(1)❌❌`❌❌❌TINYINT.UNSIGNEDSMALLINTTINYINT UNSIGNEDTINYINT UNSIGNEDTINYINTmssql-1INTEGERINTEGERSMALLINTSMALLINTTINYINT.ZEROFILL❌TINYINT ZEROFILLTINYINT ZEROFILL❌❌❌❌❌SMALLINTSMALLINTSMALLINTSMALLINTSMALLINTINTEGERINTEGERSMALLINTSMALLINTSMALLINT(1)❌SMALLINT(1)SMALLINT(1)❌❌❌❌❌SMALLINT.UNSIGNEDINTEGERints-2SMALLINT UNSIGNEDSMALLINT UNSIGNEDINTEGERints-2INTEGERINTEGERINTEGERints-2INTEGERints-2SMALLINT.ZEROFILL❌SMALLINT ZEROFILLSMALLINT ZEROFILL❌❌❌❌❌MEDIUMINTINTEGERMEDIUMINTMEDIUMINTINTEGERints-1INTEGERINTEGERINTEGERINTEGERMEDIUMINT(1)❌MEDIUMINT(1)MEDIUMINT(1)❌❌❌❌❌MEDIUMINT.UNSIGNEDINTEGERMEDIUMINT UNSIGNEDMEDIUMINT UNSIGNEDINTEGERINTEGERINTEGERINTEGERINTEGERMEDIUMINT.ZEROFILL❌MEDIUMINT ZEROFILLMEDIUMINT ZEROFILL❌❌❌❌❌INTEGERINTEGERINTEGERINTEGERINTEGERINTEGERINTEGERINTEGERINTEGERINTEGER(1)❌INTEGER(1)INTEGER(1)❌❌❌❌❌INTEGER.UNSIGNEDBIGINTINTEGER UNSIGNEDINTEGER UNSIGNEDBIGINTINTEGERINTEGERBIGINTBIGINTINTEGER.ZEROFILL❌INTEGER ZEROFILLINTEGER ZEROFILL❌❌❌❌❌BIGINTBIGINTBIGINTBIGINTBIGINTINTEGERINTEGERBIGINTBIGINTBIGINT(1)❌BIGINT(1)BIGINT(1)❌❌❌❌❌BIGINT.UNSIGNED❌BIGINT UNSIGNEDBIGINT UNSIGNED❌INTEGERINTEGER❌❌BIGINT.ZEROFILL❌BIGINT ZEROFILLBIGINT ZEROFILL❌❌❌❌❌caution

The JavaScript number type can represent ints ranging from -9007199254740991 to 9007199254740991.

If your SQL type supports integer values outside this range, we recommend using bigint or string to represent your integers.

info

Numeric options can be combined: DataTypes.INTEGER(1).UNSIGNED.ZEROFILLwill result in a column of type INTEGER(1) UNSIGNED ZEROFILL in MySQL.

Approximate Decimal Numbers

The types in the following table are typically represented as an IEEE 754 floating point number, like the JavaScript number type.

FLOAT is meant to be a single-precision floating point type.DOUBLE is meant to be a double-precision floating point type.Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiFLOATREALFLOATFLOATREALREALsqlite-3FLOATsnowflake-1REALREALFLOAT(11, 10)❌FLOAT(11,10)FLOAT(11,10)❌❌❌❌❌FLOAT.UNSIGNEDREALFLOAT UNSIGNEDFLOAT UNSIGNEDREALREALFLOATREALREALFLOAT.ZEROFILL❌FLOAT ZEROFILLFLOAT ZEROFILL❌❌❌❌❌DOUBLEDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONREALFLOATDOUBLEDOUBLEDOUBLE(11, 10)❌DOUBLE PRECISION(11, 10)DOUBLE PRECISION(11, 10)❌❌❌❌❌DOUBLE.UNSIGNEDDOUBLE PRECISIONDOUBLE PRECISION UNSIGNEDDOUBLE PRECISION UNSIGNEDDOUBLE PRECISIONREALFLOATDOUBLEDOUBLEDOUBLE.ZEROFILL❌DOUBLE PRECISION ZEROFILLDOUBLE PRECISION ZEROFILL❌❌❌❌❌info

Numeric options can be combined: DataTypes.FLOAT(1, 2).UNSIGNED.ZEROFILLwill result in a column of type FLOAT(1, 2) UNSIGNED ZEROFILL in MySQL.

Exact Decimal NumbersDECIMAL is meant to be an unconstrained decimal type.DECIMAL(precision, scale) is meant to be a constrained decimal type.Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiDECIMALDECIMAL❌❌❌❌❌❌❌DECIMAL(11, 10)DECIMAL(11, 10)DECIMAL(11,10)DECIMAL(11,10)DECIMAL(11,10)❌DECIMAL(11,10)DECIMAL(11,10)DECIMAL(11,10)DECIMAL(p, s).UNSIGNEDDECIMAL(p, s)DECIMAL(p, s) UNSIGNEDDECIMAL(p, s) UNSIGNEDDECIMAL(p, s)❌DECIMAL(p, s)DECIMAL(p, s)DECIMAL(p, s)DECIMAL(p, s).ZEROFILL❌DECIMAL(p, s) ZEROFILLDECIMAL(p, s) ZEROFILL❌❌❌❌❌caution

Exact Decimal Numbers are not representable in JavaScript yet. The JavaScript number type is a double-precision 64-bit binary format IEEE 754 value, better represented by Approximate Decimal types.

To avoid any loss of precision, we recommend using string to represent Exact Decimal Numbers in JavaScript.

info

Numeric options can be combined: DataTypes.DECIMAL(1, 2).UNSIGNED.ZEROFILLwill result in a column of type DECIMAL(1, 2) UNSIGNED ZEROFILL in MySQL.

DatesSequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiDATETIMESTAMP WITH TIME ZONEDATETIMEDATETIMEDATETIMEOFFSETTEXTTIMESTAMPTIMESTAMPTIMESTAMPDATE(6)TIMESTAMP(6) WITH TIME ZONEDATETIME(6)DATETIME(6)DATETIMEOFFSET(6)TEXTTIMESTAMP(6)TIMESTAMP(6)TIMESTAMP(6)DATEONLYDATEDATEDATEDATETEXTDATEDATEDATETIMETIMETIMETIMETIMETEXTTIMETIMETIMETIME(6)TIME(6)TIME(6)TIME(6)TIME(6)TEXTTIME(6)TIME(6)TIME(6)Built-in Default Values for Dates

Along with regular default values, Sequelize provides DataTypes.NOW which will use the appropriate native SQL function based on your dialect.

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiNOWNOWNOWNOWGETDATE()❌NOWCURRENT TIMENOWMyModel.init({ myDate: { type: DataTypes.DATE, defaultValue: DataTypes.NOW, },});caution

The generation of values for DataTypes.UUIDV1 and DataTypes.UUIDV4, DataTypes.NOW and other JavaScript functions are not handled by the Database, but by Sequelize itself. This means that they will only be used when using Model methods. They will not be used in raw queries, in migrations, and all other places where Sequelize does not have access to the Model.

Read about SQL based alternatives in Dynamic SQL default values.

UUIDs

For UUIDs, use DataTypes.UUID. It becomes the UUID data type for PostgreSQL and SQLite, and CHAR(36) for MySQL.

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiUUIDUUIDCHAR(36) BINARYCHAR(36) BINARYUNIQUEIDENTIFIERTEXTVARCHAR(36)CHAR(36) FOR BIT DATACHAR(36) FOR BIT DATABuilt-in Default Values for UUID

Sequelize can generate UUIDs automatically for these attributes, simply use DataTypes.UUIDV1 or DataTypes.UUIDV4 as the default value:

MyModel.init({ myUuid: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, // Or DataTypes.UUIDV1 },});caution

Read about SQL based alternatives in Dynamic SQL default values.

BLOBs

The blob datatype allows you to insert data both as strings and buffers. However, when a blob is retrieved from database with Sequelize, it will always be retrieved as a Node Buffer.

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiBLOBBYTEABLOBBLOBVARBINARY(MAX)BLOBBLOBBLOB(1M)BLOB(1M)BLOB('tiny')BYTEATINYBLOBTINYBLOBVARBINARY(256)BLOBTINYBLOBBLOB(255)BLOB(255)BLOB('medium')BYTEAMEDIUMBLOBMEDIUMBLOBVARBINARY(MAX)BLOBMEDIUMBLOBBLOB(16M)BLOB(16M)BLOB('long')BYTEALONGBLOBLONGBLOBVARBINARY(MAX)BLOBLONGBLOBBLOB(2G)BLOB(2G)ENUMsnote

Enums are only available in PostgreSQL, MariaDB, and MySQL

The ENUM is a data type that accepts only a few values, specified as a list.

DataTypes.ENUM('foo', 'bar') // An ENUM with allowed values 'foo' and 'bar'

See the API Reference for DataTypes.ENUM for more information about the options this DataType accepts.

JSON & JSONB

The DataTypes.JSON data type is only supported for SQLite, MySQL, MariaDB and PostgreSQL. However, there is a minimum support for MSSQL (see below).

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiJSONJSONJSONJSONNVARCHAR(MAX)TEXT❌❌❌JSONBJSONB❌❌❌❌❌❌❌

The JSON data type in PostgreSQL stores the value as plain text, as opposed to binary representation.

If you simply want to store and retrieve a JSON representation, using JSON will take less disk space and less time to build from its input representation. However, if you want to do any operations on the JSON value, you should prefer the JSONB data type.

Querying JSON

Sequelize provides a special syntax to query the contents of a JSON object. Read more about querying JSON.

SQL NULL vs JSON 'null'

If your column is nullable, be aware that inserting null will insert the JSON 'null' value by default instead of the SQL NULL value.

Read more about how null is handled in JSON columns

Miscellaneous DataTypesSequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiGEOMETRYGEOMETRYGEOMETRYGEOMETRY❌❌❌❌❌GEOMETRY('POINT')GEOMETRY(POINT)POINTPOINT❌❌❌❌❌GEOMETRY('POINT', 4326)GEOMETRY(POINT,4326)❌❌❌❌❌❌❌GEOMETRY('POLYGON')GEOMETRY(POLYGON)POLYGONPOLYGON❌❌❌❌❌GEOMETRY('LINESTRING')GEOMETRY(LINESTRING)LINESTRINGLINESTRING❌❌❌❌❌GEOGRAPHYGEOGRAPHY❌❌❌❌❌❌❌HSTOREHSTORE❌❌❌❌❌❌❌note

In Postgres, the GEOMETRY and GEOGRAPHY types are implemented by the PostGIS extension.

DataTypes exclusive to PostgreSQLArraysnote

Arrays are only available in PostgreSQL.

// Defines an array of DataTypes.SOMETHING.DataTypes.ARRAY(/* DataTypes.SOMETHING */)// VARCHAR(255)[]DataTypes.ARRAY(DataTypes.STRING)// VARCHAR(255)[][]DataTypes.ARRAY(DataTypes.ARRAY(DataTypes.STRING))Rangesnote

Ranges are only available in PostgreSQL.

DataTypes.RANGE(DataTypes.INTEGER) // int4rangeDataTypes.RANGE(DataTypes.BIGINT) // int8rangeDataTypes.RANGE(DataTypes.DATE) // tstzrangeDataTypes.RANGE(DataTypes.DATEONLY) // daterangeDataTypes.RANGE(DataTypes.DECIMAL) // numrange

Since range types have extra information for their bound inclusion/exclusion it's not very straightforward to just use a tuple to represent them in JavaScript.

When supplying ranges as values you can choose from the following APIs:

// defaults to inclusive lower bound, exclusive upper boundconst range = [ new Date(Date.UTC(2016, 0, 1)), new Date(Date.UTC(2016, 1, 1))];// '["2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'// control inclusionconst range = [ { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false }, { value: new Date(Date.UTC(2016, 1, 1)), inclusive: true },];// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]'// composite formconst range = [ { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false }, new Date(Date.UTC(2016, 1, 1)),];// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'const Timeline = sequelize.define('Timeline', { range: DataTypes.RANGE(DataTypes.DATE)});await Timeline.create({ range });

However, retrieved range values always come in the form of an array of objects. For example, if the stored value is ("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"], after a finder query you will get:

[ { value: Date, inclusive: false }, { value: Date, inclusive: true }]

You will need to call reload() after updating an instance with a range type or use the returning: true option.

Special Cases// empty range:Timeline.create({ range: [] }); // range = 'empty'// Unbounded range:Timeline.create({ range: [null, null] }); // range = '[,)'// range = '[,"2016-01-01 00:00:00+00:00")'Timeline.create({ range: [null, new Date(Date.UTC(2016, 0, 1))] });// Infinite range:// range = '[-infinity,"2016-01-01 00:00:00+00:00")'Timeline.create({ range: [-Infinity, new Date(Date.UTC(2016, 0, 1))] });In TypeScript

Use the Range type provided by Sequelize to properly type your range:

import { Model, InferAttributes, Range } from '@sequelize/core';class User extends Model { declare myDateRange: Range;}User.init({ myDateRange: { type: DataTypes.RANGE(DataTypes.DATE), allowNull: false, }});Network AddressesSequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmiCIDRCIDR❌❌❌❌❌❌❌INETINET❌❌❌❌❌❌❌MACADDRMACADDR❌❌❌❌❌❌❌Virtual

DataTypes.VIRTUAL is a special DataType used to declare virtual attributes. It does not create an actual column.

caution

Unlike GENERATED columns, DataTypes.VIRTUAL columns are handled in the JavaScript Layer. They are not created on the database table. See the issue about generated columns to learn more.

Custom Data Types

Databases support more Data Types that are not covered by the ones built-in in Sequelize. If you need to use such a Data Types, you can create your own DataType.

It is also possible to use a raw SQL string as the type of your attribute. This string will be used as-is as the type of your column when creating the table.

User = sequelize.define('user', { password: { type: 'VARBINARY(50)', },});

Caution: Sequelize will not do any extra type transformation or validation on an attribute declared like this. Use wisely!

And, of course, you can open a feature request in the Sequelize repository to request the addition of a new built-in DataType.

When an int type is not available, Sequelize uses a bigger int type.↩When an unsigned int type is not available, Sequelize uses a bigger int type to ensure the type covers all possible unsigned integer values of the smaller int type.↩TINYINT in SQL Server is unsigned. DataTypes.TINYINT.UNSIGNED therefore maps to TINYINT, and DataTypes.TINYINT maps to SMALLINT.↩Unlike other dialects, in Snowflake, FLOAT is a double-precision floating point number type.↩Unlike other dialects, in SQLite, REAL is a double-precision floating point number type.↩

【本文地址】

Data Types

Data Types

今日新闻

推荐新闻